*open the police arrest data with the victim information
use "W:\original\u1067_al3_rpk.dta", clear

*Renaming below
rename rnimi crime /*crime name*/
rename rkdi	crimecode6
rename juttu_ind case_index
rename ao_ika	age_plaintiff /*age of plaintiff*/


rename re_sukup	suspect_sex
rename re_ika age_suspect 

rename ao_sukup	plaintiff_sex
rename juttu_koodi1	economic /*case  code 1, see description (police)*/
rename juttu_koodi2	race
rename juttu_koodi3 domestic
rename juttu_koodi4 it
rename juttu_koodi5 drug  


*crime code for domestic violence: the last three are added for workplace crimes for the various robustness checks in the paper
gen pot_domestic=0
replace pot_domestic=1 if crimecode6=="200101" |  crimecode6=="200104" | crimecode6== "200183" | crimecode6== "200184" | crimecode6== "200201" | crimecode6== "200202" | crimecode6== "200401" | crimecode6== "200402" | crimecode6== "200501" | crimecode6== "200503" | crimecode6== "2005A1" | crimecode6== "200601" | crimecode6== "200603" | crimecode6== "200701" | crimecode6== "200702" | crimecode6== "200801" | crimecode6== "200804" | crimecode6== "2008A1" | crimecode6== "2008A3" | crimecode6== "2008B1" | crimecode6== "2008B3" | crimecode6== "2008C1" | crimecode6== "2008C2" | crimecode6== "2507A1" | crimecode6==  "250801" | crimecode6==  "250301" | crimecode6==  "2503A1" | crimecode6==  "2503A3"| crimecode6==  "250101"  | crimecode6==  "250201" | crimecode6==  "250303" | crimecode6==  "250701" | crimecode6==   "310101" | crimecode6==  "310102" | crimecode6==  "310301" | crimecode6==  "310302"| crimecode6==  "210101" | crimecode6== "210102" | crimecode6== "210201" | crimecode6==  "210202" | crimecode6== "210301" | crimecode6== "210302" | crimecode6== "210401" | crimecode6== "210402" | crimecode6==  "210501" | crimecode6== "210502" | crimecode6== "210601" | crimecode6== "210602" | crimecode6== "2106A1" | crimecode6== "210701" | crimecode6== "210801" | crimecode6== "210901" | crimecode6== "211001" | crimecode6== "211101"| crimecode6== "211201" | crimecode6== "211301" | crimecode6==  "211401" | crimecode6== "211501" | crimecode6=="2503B1" | crimecode6=="2503B2" | crimecode6=="2503C1" | crimecode6=="470301" | crimecode6=="4703A1" | crimecode6=="111101"

gen crime_chapter=substr(crimecode6,1,2)
drop if crime_chapter=="23"



*Renaming below

rename ilmoitusvuosi year_announcement
rename kuittausvuosi year_record


gen dom=domestic!=""

keep if re_shnro!=""





duplicates drop

rename  taika	date_event
gen year_event=substr(date_event,-4,.)
destring year_event, replace
gen year=year_event
gen year_event_crime=year if (dom==1 | pot_domestic==1)

sort re_shnro year

gen record=1



/*check what is the pkupm* reporting date*/
gen pkupvm_reported=pkupvm!=""
gsort s_tapausid re_shnro ao_shnro date_event pkupvm_reported crimecode6 year_event pkupvm_reported

duplicates report s_tapausid re_shnro ao_shnro date_event crimecode6 year_event s_juttu

 
duplicates drop s_tapausid re_shnro ao_shnro date_event crimecode6 year_event, force
duplicates drop re_shnro year_event crimecode6, force

rename s_tapausid	case_id



collapse (sum) record, by(re_shnro year)

bysort re_shnro (year): gen cum_record=sum(record)

rename re_shnro shnro 
save "W:\workplace\data\crime_records_year", replace

preserve 
keep shnro 
duplicates drop shnro, force 
expand 25
bysort shnro: gen year=1994+_n
save "W:\workplace\data\tempfile", replace 
restore 


use "W:\workplace\data\tempfile", clear 
merge 1:1 shnro year using "W:\workplace\data\crime_records_year"
drop if _merge==2
drop _merge 


replace record=0 if record==. 
drop cum_record 
bysort shnro (year): gen cum_record=sum(record)

save "W:\workplace\data\crime_records_year", replace

forvalues Y=1995 (1) 2019{
preserve 
keep if year==`Y'
save "${dataout}crime_records_year_`Y'", replace 
restore 

}
